﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using KingRoad.EPM.SafetyManagementBLL.UIRules;
using KingRoad.EPM.SafetyManagementEntity.Model;
using KingRoad.EPM.Web.Areas.SafetyConstruction.ViewModels;
using KingRoad.EPM.Web.Areas.SafetyConstruction.SafetyConstructionFramework.Common;

namespace KingRoad.EPM.Web.Areas.SafetyConstruction.Controllers
{
    public class ScmCostLedgerController : BaseController
    {
        /// <summary>
        /// 奖励/惩罚台账-列表
        /// </summary>
        /// <returns></returns>
        public ActionResult CostLedgerList(string pageName)
        {
            int totalRecord = 0;
            //当前页码
            ViewBag.pageNum = 1;
            //每页多少条
            ViewBag.numPerPage = 20;
            var costledgerList = ScmCostledgerUIRule.GetAllCostLedgerForPage(ViewBag.pageNum, ViewBag.numPerPage, "Cl_OperateTime", out totalRecord, " Cl_SectionName in (" + Get_JL_JC_ZDJL_SG_SegmentCodes() + ") ");
            //总条数
            ViewBag.totalcount = totalRecord;
            //总页数
            ViewBag.pagenumshown = totalRecord % ViewBag.numPerPage == 0 ? totalRecord / ViewBag.numPerPage : totalRecord / ViewBag.numPerPage + 1;
         //   return View(new ScmCostledgerViewModel { TheCostLedgerList = costledgerList });
            return String.IsNullOrEmpty(pageName) ? View(new ScmCostledgerViewModel { TheCostLedgerList = costledgerList }) : View(pageName, new ScmCostledgerViewModel { TheCostLedgerList = costledgerList });
        }
        /// <summary>
        /// 奖励/惩罚台账-报表
        /// </summary>
        /// <returns></returns>
        public ActionResult CostLedgerReport(string type)
        {
            ViewBag.Type = type;
            ViewBag.TypeName = "奖励";
            if (type == "0")
                ViewBag.TypeName = "奖励";
            if (type == "1")
                ViewBag.TypeName = "罚款";

            //获取全部信息
            var CostledgerReport = ScmCostledgerUIRule.GetCostLedgerReport(type,null,null);

            //增加表头(前两行)
            DataTable dthead = new DataTable();
            dthead.Columns.Add("年份", typeof(string));
          
            DataRow dr1 = dthead.NewRow();
            DataRow dr2 = dthead.NewRow();
            dr1["年份"] = "清单安全费用（1.5%)";
            dr2["年份"] = "提取20%费用";
            decimal total1 = 0;
            decimal total2 = 0;
       
            for (int i = 1; i < CostledgerReport.Columns.Count; i++)
			{
                dthead.Columns.Add(CostledgerReport.Columns[i].ColumnName);
                var ps = ScmSecurecostUIRule.GetInfoBySql(" Sc_Section = '" + CostledgerReport.Columns[i].ColumnName + "' ");
                if (ps!=null)
                {
                    dr1[CostledgerReport.Columns[i].ColumnName] = ps.Sc_Cost;
                    dr2[CostledgerReport.Columns[i].ColumnName] = ps.Sc_Cost * 2 / 10;
                    total1 += ps.Sc_Cost;
                    total2 += ps.Sc_Cost * 2 / 10;
                }
            
            }
            dthead.Columns.Add("合计", typeof(string));
            dr1["合计"] = total1;
            dr2["合计"] = total2;
            dthead.Rows.Add(dr1);
            dthead.Rows.Add(dr2);
            //遍历求合计
          
            DataTable dt = new DataTable();
            DateTime min = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM") + "-02");
            if (CostledgerReport.Rows.Count>0)
            {
                 min = Convert.ToDateTime(CostledgerReport.Rows[0]["cl_yearmonth"]);
            }
            
            DateTime now = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM")+"-01");
            CostledgerReport.Columns.Add("合计", typeof(string)).SetOrdinal(CostledgerReport.Columns.Count-1);
            foreach (DataRow row in CostledgerReport.Rows)
            {
                decimal totalyear = 0;
                for (int i = 0; i < CostledgerReport.Columns.Count - 1; i++)
                {

                    if (i != 0)
                    {

                        if (row[CostledgerReport.Columns[i].ColumnName] == System.DBNull.Value)
                        {
                            row[CostledgerReport.Columns[i].ColumnName] = 0;
                        }
                        totalyear += decimal.Parse(row[CostledgerReport.Columns[i].ColumnName].ToString());
                    }
                }
                row["合计"] = totalyear.ToString();

            }
              //合计累加
            int t = 0; 
            for (int j = 0; min <= now; j++)
            {
                DataRow dr = CostledgerReport.NewRow();
                DataRow totaldr = CostledgerReport.NewRow();


                if (min.Month == 12 || min == now)
                {
                    DateTime starttime = Convert.ToDateTime(min.Year+"-01-01");
                    DateTime endtime = Convert.ToDateTime(min.Year+"-12-01");
                    var CostledgerReportYear = ScmCostledgerUIRule.GetCostLedgerReport(type, starttime, endtime);
                    dr["cl_yearmonth"] =  min.Year + "-01-02";
                    decimal totalcell = 0;
                    for (int i = 0; i <  CostledgerReport.Columns.Count-1; i++)
                    {
                        decimal totalyear = 0;
                        if (i!=0)
                        {  
                            foreach (DataRow row in CostledgerReportYear.Rows)
                            {
                                if (row[CostledgerReportYear.Columns[i].ColumnName] == System.DBNull.Value)
                                {
                                    row[CostledgerReportYear.Columns[i].ColumnName] = 0;
                                }
                                totalyear += decimal.Parse(row[CostledgerReportYear.Columns[i].ColumnName].ToString());
                            }
                            dr[CostledgerReportYear.Columns[i].ColumnName] = totalyear.ToString();
                            totalcell += totalyear;
                        }
                     
                     }
                    dr["合计"] = totalcell;
                    CostledgerReport.Rows.InsertAt(dr, t + j + 1);
                    t++;
               }
              
                min = min.AddMonths(1);
            }
            //add 最后一行合计
            //增加表头(前两行)
            DataTable dtend = new DataTable();
            dtend.Columns.Add("年份", typeof(string));

            DataRow drend = dtend.NewRow();
            drend["年份"] = ViewBag.TypeName + "合计";
           
            decimal totalend = 0;
            

            for (int i = 1; i < CostledgerReport.Columns.Count-1; i++)
            {
                dtend.Columns.Add(CostledgerReport.Columns[i].ColumnName);
                var dte =DateTime.Now;
                if (CostledgerReport.Rows.Count>0)
                {
                    dte = Convert.ToDateTime(CostledgerReport.Rows[0]["cl_yearmonth"]);
                }
                var CostledgerReportTotal = ScmCostledgerUIRule.GetCostLedgerReport(type, dte, now);
               
                    decimal totalyear = 0;
                    if (i != 0)
                    {
                        foreach (DataRow row in CostledgerReportTotal.Rows)
                        {
                            if (row[CostledgerReportTotal.Columns[i].ColumnName] == System.DBNull.Value)
                            {
                                row[CostledgerReportTotal.Columns[i].ColumnName] = 0;
                            }
                            totalyear += decimal.Parse(row[CostledgerReportTotal.Columns[i].ColumnName].ToString());
                        }
                        drend[CostledgerReportTotal.Columns[i].ColumnName] = totalyear.ToString();
                        totalend += totalyear;
                    }
            }
            dtend.Columns.Add("合计", typeof(string));
            drend["合计"] = totalend;

            dtend.Rows.Add(drend);


            return View(new ScmCostledgerViewModel { dt = CostledgerReport, dthead = dthead, dtend=dtend });
        }
        // 报表导出
        public ActionResult ExportExcelTest(string tableHtml, string type)
        {
            ViewBag.TypeName = "奖励";
            if (type == "0")
                ViewBag.TypeName = "奖励";
            if (type == "1")
                ViewBag.TypeName = "罚款";
            var htmlTable = System.Web.HttpUtility.UrlDecode(tableHtml);

            var loadOptions = new LoadOptions(LoadFormat.Html);
            Stream stream = new MemoryStream(Encoding.UTF8.GetBytes(htmlTable));
            var wb = new Workbook(stream, loadOptions);
            wb.Worksheets[0].AutoFitColumns();
            wb.Worksheets[0].IsGridlinesVisible = true;

            var fileName = ViewBag.TypeName + "台帐.xls";
            string serverPath = Server.MapPath("~/Areas/SafetyConstruction/Upload/Temp/" + fileName);
            wb.Save(serverPath, SaveFormat.Excel97To2003);

            var filePath = "../../Areas/SafetyConstruction/Upload/Temp/" + fileName;
            var result = "{\"src\" : \"" + filePath + "\"}";
            return Content(result);
        }
        /// <summary>
        /// 奖励/惩罚台账-添加 
        /// 功能：显示添加页面
        /// </summary>
        /// <returns></returns>
        public ActionResult CostLedgerAdd()
        {
            List<SelectListItem> list = new List<SelectListItem>();
            list.Add(new SelectListItem { Text = "奖励", Value = "false" });
            list.Add(new SelectListItem { Text = "罚款", Value = "true" });
            ViewBag.list = list;
            return View();
        }
        /// <summary>
        /// 奖励/惩罚台账-添加 
        /// 功能：执行添加操作
        /// </summary>
        /// <returns></returns>
        public JsonResult CostLedgerAddData(ScmCostledgerViewModel vm)
        {
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                vm.TheScmCostledgerInfo.Cl_ID = Guid.NewGuid().ToString();
                vm.TheScmCostledgerInfo.Cl_OperateUserID = GetLoginUserId;              //操作人ID及名称 添加权限时赋值
                vm.TheScmCostledgerInfo.Cl_OperateUserName = GetLoginUserRealName;
                vm.TheScmCostledgerInfo.Cl_OperateTime = DateTime.Now;
                int safe = ScmCostledgerUIRule.AddCostledger(vm.TheScmCostledgerInfo);
            });

            dynamic jsonReturn = new ExpandoObject();

            //保存成功
            if (isScuess)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "添加成功",
                    navTabId = "SafetyConstruction.ScmCostLedger.CostLedgerList",
                    rel = "",
                    callbackType = "closeCurrent",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "添加失败",
                };
            }

            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// 奖励/惩罚台账-删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public JsonResult CostLedgerDelete(string id)
        {
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                int safe = ScmCostledgerUIRule.DeleteCostledger(id);
            });
            dynamic jsonReturn = new ExpandoObject();
            //删除成功
            if (isScuess)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "删除成功",
                    navTabId = "SafetyConstruction.ScmCostLedger.CostLedgerList",
                    rel = "",
                    callbackType = "",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "删除失败",
                };
            }
            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// 奖励/惩罚台账-批量删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public JsonResult CostLedgerMultiDelete()
        {
            dynamic jsonReturn = new ExpandoObject();
            var ids = base.GetRequestFormValue<string>("ids");
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                int safe = ScmCostledgerUIRule.DeleteCostledgerMulti(ids);
            });

            //保存成功
            if (true)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "删除成功",
                    navTabId = "SafetyConstruction.ScmCostLedger.CostLedgerList",
                    rel = "",
                    callbackType = "",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "删除失败",
                };
            }
            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// 奖励/惩罚台账——编辑
        /// 功能：根据id加载相对应数据
        /// </summary>
        /// <returns></returns>
        public ActionResult CostLedgerEdit(Guid id)
        {
            List<SelectListItem> list = new List<SelectListItem>();
            list.Add(new SelectListItem { Text = "奖励", Value = "false" });
            list.Add(new SelectListItem { Text = "罚款", Value = "true" });
            ViewBag.list = list;
            var CostLedgerInfo = ScmCostledgerUIRule.GetCostledgerByID(id);
            return View(new ScmCostledgerViewModel { TheScmCostledgerInfo = CostLedgerInfo });
            //return View("CostLedgerAdd");
        }
        /// <summary>
        /// 奖励/惩罚台账-编辑（保存工作） 
        /// 功能：执行保存数据
        /// </summary>
        /// <returns></returns>
        public JsonResult CostLedgerEditData(ScmCostledgerViewModel vm)
        {
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                vm.TheScmCostledgerInfo.Cl_OperateUserID = GetLoginUserId;              //操作人ID及名称 添加权限时赋值
                vm.TheScmCostledgerInfo.Cl_OperateUserName = GetLoginUserRealName;
                vm.TheScmCostledgerInfo.Cl_OperateTime = DateTime.Now;
                int safe = ScmCostledgerUIRule.UpdateCostledger(vm.TheScmCostledgerInfo);
            });

            dynamic jsonReturn = new ExpandoObject();

            //保存成功
            if (isScuess)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "更新成功",
                    navTabId = "SafetyConstruction.ScmCostLedger.CostLedgerList",
                    rel = "",
                    callbackType = "closeCurrent",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "更新失败",
                };
            }

            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// Excel导出
        /// </summary>
        /// <returns></returns>
        public FileResult Excel()
        {
            //获取list数据           
            //var list = bll.NurseUserListExcel("", "ID asc");
            var list = ScmCostledgerUIRule.GetAllCostledger();

            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("序号");
            row1.CreateCell(1).SetCellValue("标段名称");
            row1.CreateCell(2).SetCellValue("单位");
            row1.CreateCell(3).SetCellValue("类型");
            row1.CreateCell(4).SetCellValue("年份");
            row1.CreateCell(5).SetCellValue("金额");
            row1.CreateCell(6).SetCellValue("备注");
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(i + 1);
                rowtemp.CreateCell(1).SetCellValue(list[i].Cl_SectionName);
                rowtemp.CreateCell(2).SetCellValue(list[i].Cl_ConstructionName);
                rowtemp.CreateCell(3).SetCellValue(list[i].Cl_Type==true?"罚款":"奖励");
                rowtemp.CreateCell(4).SetCellValue(list[i].Cl_YearMonth.ToString("yyyy年M月"));
                rowtemp.CreateCell(5).SetCellValue(list[i].Cl_Amount.ToString());
                rowtemp.CreateCell(6).SetCellValue(list[i].Cl_Remark);
               
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            //return File(ms, "application/vnd.ms-excel", "用户信息.xls");
            return File(ms, "application/vnd.ms-excel", "奖励/惩罚台账.xls");

        }
        
              

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="vm"></param>
        /// <returns></returns>
        public ActionResult SplitPage(ScmCostledgerViewModel vm)
        {
            int totalRecord = 0;
            //当前页码
            ViewBag.pageNum = Convert.ToInt32(Request.Form["pageNum"]);
            //每页多少条
            ViewBag.numPerPage = Convert.ToInt32(Request.Form["numPerPage"]);
            if (ViewBag.pageNum == 0 || ViewBag.numPerPage == 0)
                return CostLedgerList("CostLedgerList");

            ViewBag.iput_biaoduan = Request.Form["iput_biaoduan"];
            ViewBag.iput_unit = Request.Form["iput_unit"];
            ViewBag.iput_timestart = Request.Form["iput_timestart"];
            ViewBag.iput_timeend = Request.Form["iput_timeend"];
            ViewBag.select_type = Request.Form["select_type"].ToString();

            string sql = " Cl_SectionName in (" + Get_JL_JC_ZDJL_SG_SegmentCodes() + ") ";
            if (ViewBag.iput_biaoduan != "")
                sql += " and Cl_SectionName like '%" + ViewBag.iput_biaoduan + "%' ";
            if (ViewBag.iput_timestart != "")
                sql += " and Cl_YearMonth>='" + Convert.ToDateTime(ViewBag.iput_timestart) + "' ";
            if (ViewBag.iput_timeend != "")
                sql += " and Cl_YearMonth<='" + Convert.ToDateTime(ViewBag.iput_timeend) + "' ";
            if (ViewBag.iput_unit != "")
                sql += " and Cl_ConstructionName like '%" + ViewBag.iput_unit + "%' ";
            if (ViewBag.select_type != "")
                sql += " and Cl_Type = '" + ViewBag.select_type + "' ";
            var costledgerList = ScmCostledgerUIRule.GetAllCostLedgerForPage(ViewBag.pageNum, ViewBag.numPerPage, "Cl_OperateTime", out totalRecord, sql);
            //总条数
            ViewBag.totalcount = totalRecord;
            //总页数
            ViewBag.pagenumshown = totalRecord % ViewBag.numPerPage == 0 ? totalRecord / ViewBag.numPerPage : totalRecord / ViewBag.numPerPage + 1;
            return View("CostLedgerList", new ScmCostledgerViewModel { TheCostLedgerList = costledgerList });
        }




    }
}
